package dao.concret;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import dao.AbstractTable;
import dao.DAOList;
import dao.tables.INSTRUMENT;

public class InstrumentDAO extends DAOList<INSTRUMENT> {

	@Override
	public INSTRUMENT find(long id) {
		INSTRUMENT inst = new INSTRUMENT();
		try {
			Statement stmt = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			ResultSet result = stmt.executeQuery("SELECT * FROM INSTRUMENT WHERE idinstrument = "
				+ id);
			if (result.first()) inst = new INSTRUMENT(id, result.getString("INSTRUMENT"));
			stmt.close();
			result.close();
			stmt = null;
			result = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return inst;
	}

	@Override
	public INSTRUMENT create(AbstractTable at) {
		INSTRUMENT obj = (INSTRUMENT) at;
		try {

			PreparedStatement prepare = this.connect
				.prepareStatement("INSERT INTO INSTRUMENT (INSTRUMENT) VALUES (?)");
			prepare.setString(1, obj.getValue());
			prepare.executeUpdate();

			ResultSet result = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE).executeQuery(
				"SELECT SEQ_INST_IDINST.currval AS id FROM dual");

			result.first();
			long id = result.getLong("id");
			obj = this.find(id);
			prepare.close();
			result.close();
			prepare = null;
			result = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return obj;
	}

	@Override
	public INSTRUMENT update(AbstractTable at) {
		INSTRUMENT obj = (INSTRUMENT) at;
		try {
			this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE).executeUpdate(
				"UPDATE INSTRUMENT SET INSTRUMENT.INSTRUMENT = '" + obj + "'"
					+ " WHERE IDINSTRUMENT = " + obj.getId());
			obj = this.find(obj.getId());
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return obj;
	}

	@Override
	public INSTRUMENT delete(AbstractTable at) {
		INSTRUMENT obj = (INSTRUMENT) at;
		try {
			Statement stmt = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			stmt.executeUpdate("DELETE FROM INSTRUMENT WHERE IDINSTRUMENT = " + obj.getId());
			stmt.close();
			stmt = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return obj;
	}

	@Override
	public ArrayList<INSTRUMENT> list() {
		ArrayList<INSTRUMENT> list = new ArrayList<INSTRUMENT>();
		try {
			Statement stmt = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			ResultSet result = stmt.executeQuery("SELECT IDINSTRUMENT FROM INSTRUMENT");
			while (result.next())
				list.add(this.find(result.getLong("IDINSTRUMENT")));
			stmt.close();
			result.close();
			stmt = null;
			result = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return list;
	}

	@Override
	public ArrayList<INSTRUMENT> list(long id) {
		ArrayList<INSTRUMENT> list = new ArrayList<INSTRUMENT>();
		try {
			Statement stmt = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
			ResultSet result = stmt
				.executeQuery("SELECT IDINSTRUMENT FROM JOUER WHERE IDPERSONNE = " + id);
			while (result.next())
				list.add(this.find(result.getLong("IDINSTRUMENT")));
			stmt.close();
			result.close();
			stmt = null;
			result = null;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return list;
	}
}
